
Imports System
Imports System.Collections
Imports System.Data.SqlClient
Imports _2bMatesSite.Common


Namespace _2bMatesSite.BO
	Public Class MessageException
		Inherits Exception
		Public Sub New(ByVal msg As String)
			MyBase.New(msg)
		End Sub
	End Class

	''' <summary>
	''' Generic utilities related to messages.
	''' </summary>
	Public Class Messages
		Public Enum MessageType
			mtAny = 0
			mtMail = 1
			mtBulletin = 2
			mtWelcome = 3
			mtGroupBulletin = 4
			mtGroupMessage = 5
		End Enum

		Public Enum BoxType
			btAny = -1
			btOutbox = 0
			btInbox = 1
		End Enum


		'
		' Blogs
		'

		Public Shared Function GetUserBlog(ByVal userID As Integer, ByVal blogID As Integer) As Blog
			Dim be As Blog = Nothing

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("SELECT * FROM Journal WHERE ParentID IS NULL AND UID=@UID AND ID=@ID")
				cmd.Connection = conn
				cmd.Parameters.Add("@UID", userID)
				cmd.Parameters.Add("@ID", blogID)
				Dim dr As SqlDataReader = cmd.ExecuteReader()
				If dr.Read() Then
					be = New Blog(dr)
				End If
			Finally
				conn.Close()
			End Try

			Return be
		End Function

		Public Shared Function GetUserBlogEntry(ByVal userID As Integer, ByVal blogID As Integer) As Blog
			Dim be As Blog = Nothing

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("SELECT * FROM Journal WHERE UID=@UID AND ID=@ID")
				cmd.Connection = conn
				cmd.Parameters.Add("@UID", userID)
				cmd.Parameters.Add("@ID", blogID)
				Dim dr As SqlDataReader = cmd.ExecuteReader()
				If dr.Read() Then
					be = New Blog(dr)
				End If
			Finally
				conn.Close()
			End Try

			Return be
		End Function

		Public Shared Function GetBlogEntry(ByVal blogID As Integer) As Blog
			Dim be As Blog = Nothing

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("SELECT * FROM Journal WHERE ID=@ID")
				cmd.Connection = conn
				cmd.Parameters.Add("@ID", blogID)
				Dim dr As SqlDataReader = cmd.ExecuteReader()
				If dr.Read() Then
					be = New Blog(dr)
				End If
			Finally
				conn.Close()
			End Try

			Return be
		End Function

		Public Shared Function GetUserBlogs(ByVal userID As Integer) As ArrayList
			Dim jList As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("SELECT * FROM Journal WHERE ParentID IS NULL AND UID=@UID ORDER BY DATE DESC")
				cmd.Connection = conn
				cmd.Parameters.Add("@UID", userID)

				Dim dr As SqlDataReader = cmd.ExecuteReader()
				While dr.Read()
					jList.Add(New Blog(dr))
				End While
			Finally
				conn.Close()
			End Try

			Return jList
		End Function

		Public Shared Function GetNotApprovedBlogEntries(ByVal blogID As Integer) As ArrayList
			Dim jList As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("SELECT * FROM Journal WHERE ParentID=@ParentID AND Status=" + DirectCast(BaseBlog.BlogStatus.gbsInitial, Integer) + " ORDER BY DATE DESC")
				cmd.Connection = conn
				cmd.Parameters.Add("@ParentID", blogID)

				Dim dr As SqlDataReader = cmd.ExecuteReader()
				While dr.Read()
					jList.Add(New Blog(dr))
				End While
			Finally
				conn.Close()
			End Try

			Return jList
		End Function

		Public Shared Function GetUserBlogEntries(ByVal blogID As Integer) As ArrayList
			Dim jList As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("SELECT * FROM Journal WHERE ParentID=@ParentID AND Status=" + DirectCast(BaseBlog.BlogStatus.gbsApproved, Integer) + " ORDER BY DATE DESC")
				cmd.Connection = conn
				cmd.Parameters.Add("@ParentID", blogID)

				Dim dr As SqlDataReader = cmd.ExecuteReader()
				While dr.Read()
					jList.Add(New Blog(dr))
				End While
			Finally
				conn.Close()
			End Try

			Return jList
		End Function


		'
		' Group Blogs
		'

		Public Shared Function GetGroupBlog(ByVal groupID As Integer, ByVal blogID As Integer) As GroupBlog
			Dim be As GroupBlog = Nothing

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("SELECT * FROM GroupBlogs WHERE ParentID IS NULL AND GroupID=@GroupID AND ID=@ID")
				cmd.Connection = conn
				cmd.Parameters.Add("@GroupID", groupID)
				cmd.Parameters.Add("@ID", blogID)
				Dim dr As SqlDataReader = cmd.ExecuteReader()
				If dr.Read() Then
					be = New GroupBlog(dr)
				End If
			Finally
				conn.Close()
			End Try

			Return be
		End Function

		Public Shared Function GetGroupBlogEntry(ByVal groupID As Integer, ByVal blogID As Integer) As GroupBlog
			Dim be As GroupBlog = Nothing

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("SELECT * FROM GroupBlogs WHERE GroupID=@GroupID AND ID=@ID")
				cmd.Connection = conn
				cmd.Parameters.Add("@GroupID", groupID)
				cmd.Parameters.Add("@ID", blogID)
				Dim dr As SqlDataReader = cmd.ExecuteReader()
				If dr.Read() Then
					be = New GroupBlog(dr)
				End If
			Finally
				conn.Close()
			End Try

			Return be
		End Function

		Public Shared Function GetGroupBlogEntry(ByVal blogID As Integer) As GroupBlog
			Dim be As GroupBlog = Nothing

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("SELECT * FROM GroupBlogs WHERE ID=@ID")
				cmd.Connection = conn
				cmd.Parameters.Add("@ID", blogID)
				Dim dr As SqlDataReader = cmd.ExecuteReader()
				If dr.Read() Then
					be = New GroupBlog(dr)
				End If
			Finally
				conn.Close()
			End Try

			Return be
		End Function

		Public Shared Function GetGroupBlogs(ByVal groupID As Integer) As ArrayList
			Dim jList As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("SELECT * FROM GroupBlogs WHERE ParentID IS NULL AND GroupID=@GroupID ORDER BY DATE DESC")
				cmd.Connection = conn
				cmd.Parameters.Add("@GroupID", groupID)

				Dim dr As SqlDataReader = cmd.ExecuteReader()
				While dr.Read()
					jList.Add(New GroupBlog(dr))
				End While
			Finally
				conn.Close()
			End Try

			Return jList
		End Function

		Public Shared Function GetNotApprovedGroupBlogEntries(ByVal groupID As Integer) As ArrayList
			Dim jList As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("SELECT * FROM GroupBlogs WHERE GroupID=@GroupID AND Status=" + DirectCast(GroupBlog.BlogStatus.gbsInitial, Integer) + " ORDER BY DATE DESC")
				cmd.Connection = conn
				cmd.Parameters.Add("@GroupID", groupID)

				Dim dr As SqlDataReader = cmd.ExecuteReader()
				While dr.Read()
					jList.Add(New GroupBlog(dr))
				End While
			Finally
				conn.Close()
			End Try

			Return jList
		End Function

		Public Shared Function GetNotApprovedGroupBlogEntries(ByVal groupID As Integer, ByVal blogID As Integer) As ArrayList
			Dim jList As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("SELECT * FROM GroupBlogs WHERE GroupID=@GroupID AND ParentID=@ParentID AND Status=" + DirectCast(GroupBlog.BlogStatus.gbsInitial, Integer) + " ORDER BY DATE DESC")
				cmd.Connection = conn
				cmd.Parameters.Add("@GroupID", groupID)
				cmd.Parameters.Add("@ParentID", blogID)

				Dim dr As SqlDataReader = cmd.ExecuteReader()
				While dr.Read()
					jList.Add(New GroupBlog(dr))
				End While
			Finally
				conn.Close()
			End Try

			Return jList
		End Function

		Public Shared Function GetGroupBlogEntries(ByVal blogID As Integer) As ArrayList
			Dim jList As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("SELECT * FROM GroupBlogs WHERE ParentID=@ParentID AND Status=" + DirectCast(GroupBlog.BlogStatus.gbsApproved, Integer) + " ORDER BY DATE DESC")
				cmd.Connection = conn
				cmd.Parameters.Add("@ParentID", blogID)

				Dim dr As SqlDataReader = cmd.ExecuteReader()
				While dr.Read()
					jList.Add(New GroupBlog(dr))
				End While
			Finally
				conn.Close()
			End Try

			Return jList
		End Function

		' Delete all blogs for a particular group
		Public Shared Sub DeleteGroupBlogs(ByVal groupID As Integer)
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("DELETE FROM GroupBlogs WHERE GroupID=@GroupID")
				cmd.Connection = conn

				cmd.Parameters.Add("@GroupID", groupID)

				cmd.ExecuteNonQuery()
			Finally
				conn.Close()
			End Try
		End Sub

		'
		' Group Bulletins
		'

		' Get one of the group bulletins for a user (recipient)
		Public Shared Function GetUserGroupBulletin(ByVal userID As Integer, ByVal groupID As Integer, ByVal bulletinID As Integer) As GroupBulletinMessage
			Dim msg As GroupBulletinMessage = Nothing

			Dim msgList As ArrayList = GetGroupBulletins(userID, groupID, bulletinID, -1, 0, 0)
			If msgList.Count > 0 Then
				msg = DirectCast(msgList(0), GroupBulletinMessage)
			End If

			Return msg
		End Function

		' Get the group bulletins to list for a user (recipient)
		'
		' Filtering read status
		'	< 0		- non-read
		'	== 0	- doesn't matter
		'	> 0		- read
		'
		' Filtering trash
		'	< 0		- not deleted
		'	== 0	- doesn't matter
		'	> 0		- deleted
		'
		Public Shared Function GetUserGroupBulletins(ByVal userID As Integer, ByVal read As Integer, ByVal trash As Integer) As ArrayList
			Return GetGroupBulletins(userID, -1, -1, -1, read, trash)
		End Function

		' Get previous group bulletin for a user
		Public Shared Function GetUserGroupPreviousBulletin(ByVal usrID As Integer, ByVal greaterThanMessageID As Integer, ByVal read As Integer, ByVal trash As Integer) As Message
			Dim msgList As ArrayList = GetGroupMessages(-1, usrID, -1, greaterThanMessageID, read, trash)
			If msgList.Count = 0 Then
				Return Nothing
			End If

			Return DirectCast(msgList(0), Message)
		End Function

		' Get next group bulletin for a user
		Public Shared Function GetUserGroupNextBulletin(ByVal usrID As Integer, ByVal lessThanMessageID As Integer, ByVal read As Integer, ByVal trash As Integer) As Message
			Dim msgList As ArrayList = GetGroupMessages(-1, usrID, lessThanMessageID, -1, read, trash)
			If msgList.Count = 0 Then
				Return Nothing
			End If

			Return DirectCast(msgList(0), Message)
		End Function

		' Get the group bulletins that a user has posted
		'
		' Filtering read status
		'	< 0		- non-read
		'	== 0	- doesn't matter
		'	> 0		- read
		'
		' Filtering trash
		'	< 0		- not deleted
		'	== 0	- doesn't matter
		'	> 0		- deleted
		'
		Public Shared Function GetUserPostedGroupBulletins(ByVal senderID As Integer, ByVal read As Integer, ByVal trash As Integer) As ArrayList
			Return GetGroupBulletins(-1, -1, -1, senderID, read, trash)
		End Function

		' Get one of the group bulletins for a particular group
		Public Shared Function GetGroupBulletin(ByVal groupID As Integer, ByVal bulletinID As Integer) As GroupBulletinMessage
			Dim msg As GroupBulletinMessage = Nothing

			Dim msgList As ArrayList = GetGroupBulletins(-1, groupID, bulletinID, -1, 0, 0)
			If msgList.Count > 0 Then
				msg = DirectCast(msgList(0), GroupBulletinMessage)
			End If

			Return msg
		End Function


		' Get the group bulletins to list for a particular group
		'
		' Filtering read status
		'	< 0		- non-read
		'	== 0	- doesn't matter
		'	> 0		- read
		'
		' Filtering trash
		'	< 0		- not deleted
		'	== 0	- doesn't matter
		'	> 0		- deleted
		'
		Public Shared Function GetGroupBulletins(ByVal groupID As Integer, ByVal read As Integer, ByVal trash As Integer) As ArrayList
			Return GetGroupBulletins(-1, groupID, -1, -1, read, trash)
		End Function

		Public Shared Function GetGroupPreviousBulletin(ByVal grpID As Integer, ByVal greaterThanMessageID As Integer, ByVal read As Integer, ByVal trash As Integer) As Message
			Dim msgList As ArrayList = GetGroupMessages(grpID, -1, -1, greaterThanMessageID, read, trash)
			If msgList.Count = 0 Then
				Return Nothing
			End If

			Return DirectCast(msgList(0), Message)
		End Function

		Public Shared Function GetGroupNextBulletin(ByVal grpID As Integer, ByVal lessThanMessageID As Integer, ByVal read As Integer, ByVal trash As Integer) As Message
			Dim msgList As ArrayList = GetGroupMessages(grpID, -1, lessThanMessageID, -1, read, trash)
			If msgList.Count = 0 Then
				Return Nothing
			End If

			Return DirectCast(msgList(0), Message)
		End Function

		Private Shared Function GetGroupMessagesSQL(ByVal grpID As Integer, ByVal userID As Integer, ByVal lessThanMessageID As Integer, ByVal greaterThanMessageID As Integer, ByVal read As Integer, ByVal trash As Integer) As String
			Dim sql As String = "SELECT * FROM GroupBulletins WHERE" + Environment.NewLine

			If userID >= 0 Then
				sql += "ToUID=" + userID + Environment.NewLine
			Else
				sql += "ToUID IS NULL" + Environment.NewLine
			End If

			If grpID >= 0 Then
				sql += "AND ToGroupID=" + grpID + Environment.NewLine
			End If

			If lessThanMessageID >= 0 Then
				sql += "AND ID < " + lessThanMessageID + Environment.NewLine
ElseIf greaterThanMessageID >= 0 Then
				sql += "AND ID > " + greaterThanMessageID + Environment.NewLine
			End If

			If read <> 0 Then
				sql += "AND StatusRead = " + (IIf(read > 0,1,0)) + Environment.NewLine
				' Read status matters
			End If

			If trash <> 0 Then
				sql += "AND StatusDeleted = " + (IIf(trash > 0,1,0)) + Environment.NewLine
				' Trash matters
			End If

			If greaterThanMessageID >= 0 Then
				sql += "ORDER BY Date Asc, ID Asc" + Environment.NewLine
			Else
				sql += "ORDER BY Date Desc, ID Desc" + Environment.NewLine
			End If

			Return sql
		End Function

		Private Shared Function GetGroupMessages(ByVal grpID As Integer, ByVal userID As Integer, ByVal lessThanMessageID As Integer, ByVal greaterThanMessageID As Integer, ByVal read As Integer, ByVal trash As Integer) As ArrayList
			Dim msgs As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()
			Try
				Dim cmd As New SqlCommand(GetGroupMessagesSQL(grpID, userID, lessThanMessageID, greaterThanMessageID, read, trash))
				cmd.Connection = conn

				Dim dr As SqlDataReader = cmd.ExecuteReader()
				While dr.Read()
					msgs.Add(New GroupBulletinMessage(dr))
				End While
			Finally
				conn.Close()
			End Try

			Return msgs
		End Function

		' Mark the group as NULL for all group bulletins for a particular group
		Public Shared Sub RemoveGroupFromGroupBulletins(ByVal groupID As Integer)
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("UPDATE GroupBulletins SET ToGroupID=NULL WHERE ToGroupID=@ToGroupID")
				cmd.Connection = conn

				cmd.Parameters.Add("@ToGroupID", groupID)

				cmd.ExecuteNonQuery()
			Finally
				conn.Close()
			End Try
		End Sub

		Private Shared Function GetGroupBulletins(ByVal userID As Integer, ByVal groupID As Integer, ByVal messageID As Integer, ByVal senderID As Integer, ByVal read As Integer, ByVal trash As Integer) As ArrayList
			Dim msgs As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()
			Try
				Dim cmd As New SqlCommand(GetGroupBulletinsSQL(userID, groupID, messageID, senderID, read, trash))
				cmd.Connection = conn

				Dim dr As SqlDataReader = cmd.ExecuteReader()
				While dr.Read()
					msgs.Add(New GroupBulletinMessage(dr))
				End While
			Finally
				conn.Close()
			End Try

			Return msgs
		End Function

		Private Shared Function GetGroupBulletinsSQL(ByVal userID As Integer, ByVal groupID As Integer, ByVal messageID As Integer, ByVal senderID As Integer, ByVal read As Integer, ByVal trash As Integer) As String
			Dim sql As String = "SELECT * FROM GroupBulletins" + Environment.NewLine

			sql += "WHERE" + Environment.NewLine
			If userID >= 0 Then
				sql += "ToUID=" + userID + Environment.NewLine
			Else
				sql += "ToUID IS NULL" + Environment.NewLine
			End If

			If groupID >= 0 Then
				sql += "AND ToGroupID=" + groupID + Environment.NewLine
			End If

			If messageID >= 0 Then
				sql += "AND ID = " + messageID + Environment.NewLine
			End If

			If senderID >= 0 Then
				sql += "AND FromUID = " + senderID + Environment.NewLine
			End If

			If read <> 0 Then
				sql += "AND StatusRead = " + (IIf(read > 0,1,0)) + Environment.NewLine
				' Read status matters
			End If

			If trash <> 0 Then
				sql += "AND StatusDeleted = " + (IIf(trash > 0,1,0)) + Environment.NewLine
				' Trash matters
			End If

			sql += "ORDER BY Date Desc" + Environment.NewLine

			Return sql
		End Function


		'
		' Messages AND Bulletins
		'

		' Get all users messages, mixed with group bulletins received by the user
		' Additionally filter by deleted mark
		Public Shared Function GetUserMessagesGroupBulletins(ByVal userID As Integer, ByVal trash As Boolean) As ArrayList
			Dim res As New ArrayList(GetUserMessages(userID, trash))
			res.AddRange(GetUserGroupBulletins(userID, 0, IIf(trash,1,-1)))

			res.Sort(New MessageSorter(True))

			Return res
		End Function

		' Get all users messages, mixed with group bulletins received by the user
		' Additionally filter by deleted mark
		Public Shared Function GetUserInboxMessagesGroupBulletins(ByVal userID As Integer, ByVal trash As Boolean) As ArrayList
			Dim res As New ArrayList(Messages.GetUserInboxMessages(userID, trash))
			res.AddRange(GetUserGroupBulletins(userID, 0, IIf(trash,1,-1)))

			res.Sort(New MessageSorter(False))

			Return res
		End Function

		Public Shared Function GetUserInboxMessagesGroupBulletins(ByVal userID As Integer, ByVal curMessageID As Integer, ByVal msgType As Integer, ByVal isPrev As Boolean, ByVal isNext As Boolean) As Message
			Dim msgList As ArrayList = GetUserInboxMessagesGroupBulletins(userID, False)
			Dim i As Integer = 0
			If msgList.Count = 0 Then
				Return Nothing
			End If

			Dim tmpMessage As Message = Nothing
			Dim curMessageIndex As Integer = -1

			i = 0
			While i < msgList.Count
				tmpMessage = DirectCast(msgList(i), Message)
				If tmpMessage.ID = curMessageID AndAlso DirectCast(tmpMessage.MessageType, Integer) = msgType Then
					curMessageIndex = i
					Exit While
				End If
				System.Math.Max(System.Threading.Interlocked.Increment(i),i - 1)
			End While

			If curMessageIndex < 0 Then
				Throw New Exception("The message doesn't exist")
			End If

			If isPrev AndAlso curMessageIndex > 0 Then
				Return DirectCast(msgList(curMessageIndex - 1), Message)
			End If

			If isNext AndAlso curMessageIndex < msgList.Count - 1 Then
				Return DirectCast(msgList(curMessageIndex + 1), Message)
			End If

			Return Nothing
		End Function

		' Get a user message by it's ID
		' NOTE: doesn't matter if it's inbox/oubox/message/bulletin
		' if not found returns null
		Public Shared Function GetUserMessage(ByVal userID As Integer, ByVal messageID As Integer) As Message
			Dim msg As Message = Nothing

			Dim msgList As ArrayList = GetUserMessages(userID, MessageType.mtAny, BoxType.btAny, messageID)
			If msgList.Count > 0 Then
				msg = DirectCast(msgList(0), Message)
			End If

			Return msg
		End Function

		' Get a user message by it's ID and deleted mark
		' NOTE: doesn't matter if it's inbox/oubox/message/bulletin
		' if not found returns null
		Public Shared Function GetUserMessage(ByVal userID As Integer, ByVal messageID As Integer, ByVal trash As Boolean) As Message
			Dim msg As Message = Nothing

			Dim msgList As ArrayList = GetUserMessages(userID, MessageType.mtAny, BoxType.btAny, trash, messageID)
			If msgList.Count > 0 Then
				msg = DirectCast(msgList(0), Message)
			End If

			Return msg
		End Function

		' Get all users messages
		Public Shared Function GetUserMessages(ByVal userID As Integer) As ArrayList
			Dim msgList As ArrayList = GetUserMessages(userID, MessageType.mtAny, BoxType.btAny, -1)
			Return msgList
		End Function

		' Get all users messages by deleted mark
		Public Shared Function GetUserMessages(ByVal userID As Integer, ByVal trash As Boolean) As ArrayList
			Dim msgList As ArrayList = GetUserMessages(userID, MessageType.mtAny, BoxType.btAny, trash, -1)
			Return msgList
		End Function

		Public Shared Function GetUserTrashPreviousMessage(ByVal userID As Integer, ByVal greaterThanMessageID As Integer, ByVal msgType As Messages.MessageType) As Message
			'ArrayList msgList = GetUserMessages( userID, MessageType.mtAny, BoxType.btAny, true, -1, greaterThanMessageID );
			Dim msgList As ArrayList = GetUserMessagesGroupBulletins(userID, True)
			Dim messagesCount As Integer = msgList.Count
			If messagesCount = 0 Then
				Return Nothing
			End If

			Dim i As Integer = 0
			Dim msg As Message = Nothing
			i = 0
			While i < messagesCount
				msg = DirectCast(msgList(i), Message)
				If msg.ID = greaterThanMessageID AndAlso msg.MessageType = msgType AndAlso i > 0 Then
					Return DirectCast(msgList(i - 1), Message)
				End If
				System.Math.Max(System.Threading.Interlocked.Increment(i),i - 1)
			End While
			Return Nothing
		End Function
		Public Shared Function GetUserTrashNextMessage(ByVal userID As Integer, ByVal lessThanMessageID As Integer, ByVal msgType As Messages.MessageType) As Message
			'ArrayList msgList = GetUserMessages( userID, MessageType.mtAny, BoxType.btAny, true, lessThanMessageID, -1 );
			Dim msgList As ArrayList = GetUserMessagesGroupBulletins(userID, True)
			Dim messagesCount As Integer = msgList.Count
			If messagesCount = 0 Then
				Return Nothing
			End If

			Dim i As Integer = 0
			Dim msg As Message = Nothing
			i = 0
			While i < messagesCount
				msg = DirectCast(msgList(i), Message)
				If msg.ID = lessThanMessageID AndAlso msg.MessageType = msgType AndAlso i < (messagesCount - 1) Then
					Return DirectCast(msgList(i + 1), Message)
				End If
				System.Math.Max(System.Threading.Interlocked.Increment(i),i - 1)
			End While
			Return Nothing
		End Function

		' Get all new users messages
		Public Shared Function GetUserUnreadMessages(ByVal userID As Integer) As ArrayList
			Dim msgList As ArrayList = GetUserMessages(userID, MessageType.mtAny, BoxType.btAny, False)
			Return msgList
		End Function

		' Get all new users messages, filtered by deleted status
		Public Shared Function GetUserUnreadMessages(ByVal userID As Integer, ByVal trash As Boolean) As ArrayList
			Dim msgList As ArrayList = GetUserMessages(userID, MessageType.mtAny, BoxType.btAny, False, trash)
			Return msgList
		End Function


		'
		' Messages
		'

		' Inbox
		'

		Public Shared Function GetUserInboxMessage(ByVal userID As Integer, ByVal messageID As Integer, ByVal trash As Boolean) As Message
			Dim msgList As ArrayList = GetUserMessages(userID, MessageType.mtMail, BoxType.btInbox, trash, messageID)
			Return DirectCast(msgList(0), Message)
		End Function

		Public Shared Function GetUserInboxPreviousMessage(ByVal userID As Integer, ByVal greaterThanMessageID As Integer) As Message
			Dim msgList As ArrayList = GetUserMessages(userID, MessageType.mtMail, BoxType.btInbox, False, -1, greaterThanMessageID)
			If msgList.Count = 0 Then
				Return Nothing
			End If

			Return DirectCast(msgList(0), Message)
		End Function

		Public Shared Function GetUserInboxNextMessage(ByVal userID As Integer, ByVal lessThanMessageID As Integer) As Message
			Dim msgList As ArrayList = GetUserMessages(userID, MessageType.mtMail, BoxType.btInbox, False, lessThanMessageID, -1)
			If msgList.Count = 0 Then
				Return Nothing
			End If

			Return DirectCast(msgList(0), Message)
		End Function

		Public Shared Function GetUserInboxGroupBulletinPreviousMessage(ByVal userID As Integer, ByVal curMessageID As Integer, ByVal msgType As Integer) As Message
			Return GetUserInboxMessagesGroupBulletins(userID, curMessageID, msgType, True, False)
		End Function

		Public Shared Function GetUserInboxGroupBulletinNextMessage(ByVal userID As Integer, ByVal curMessageID As Integer, ByVal msgType As Integer) As Message
			Return GetUserInboxMessagesGroupBulletins(userID, curMessageID, msgType, False, True)
		End Function

		Public Shared Function GetUserInboxMessages(ByVal userID As Integer, ByVal trash As Boolean) As ArrayList
			Return GetUserMessages(userID, MessageType.mtMail, BoxType.btInbox, trash, -1)
		End Function

		Public Shared Function GetUserInboxUnreadMessages(ByVal userID As Integer) As ArrayList
			Return GetUserMessages(userID, MessageType.mtMail, BoxType.btInbox, False)
		End Function

		Public Shared Function GetUserInboxUnreadMessages(ByVal userID As Integer, ByVal trash As Boolean) As ArrayList
			Return GetUserMessages(userID, MessageType.mtMail, BoxType.btInbox, False, trash)
		End Function

		Public Shared Function GetUserOutboxMessage(ByVal userID As Integer, ByVal messageID As Integer, ByVal trash As Boolean) As Message
			Dim msgList As ArrayList = GetUserMessages(userID, BoxType.btOutbox, trash, messageID)
			Return DirectCast(msgList(0), Message)
		End Function

		Public Shared Function GetUserOutboxMessages(ByVal userID As Integer, ByVal trash As Boolean) As ArrayList
			Return GetUserMessages(userID, BoxType.btOutbox, trash, -1)
		End Function

		Public Shared Function GetUserOutboxPreviousMessage(ByVal userID As Integer, ByVal greaterThanMessageID As Integer) As Message
			Dim msgList As ArrayList = GetUserMessages(userID, MessageType.mtMail, BoxType.btOutbox, False, -1, greaterThanMessageID)
			If msgList.Count = 0 Then
				Return Nothing
			End If

			Return DirectCast(msgList(0), Message)
		End Function

		Public Shared Function GetUserOutboxNextMessage(ByVal userID As Integer, ByVal lessThanMessageID As Integer) As Message
			Dim msgList As ArrayList = GetUserMessages(userID, MessageType.mtMail, BoxType.btOutbox, False, lessThanMessageID, -1)
			If msgList.Count = 0 Then
				Return Nothing
			End If

			Return DirectCast(msgList(0), Message)
		End Function

		Public Shared Function GetUserOutboxUnreadMessages(ByVal userID As Integer) As ArrayList
			Return GetUserMessages(userID, BoxType.btOutbox, False)
		End Function

		Public Shared Function GetUserOutboxUnreadMessages(ByVal userID As Integer, ByVal trash As Boolean) As ArrayList
			Return GetUserMessages(userID, BoxType.btOutbox, False, trash)
		End Function


		' Generic (both for inbox and outbox)
		'


		' NOTE: if messageID = -1 returns all messages
		Private Shared Function GetUserMessages(ByVal userID As Integer, ByVal box As BoxType, ByVal trash As Boolean, ByVal messageID As Integer) As ArrayList
			' Inbox messages include bulletins
			' Outbox messages include only messages
			Dim msgType As MessageType = IIf((box = BoxType.btInbox),MessageType.mtAny,MessageType.mtMail)
			Return GetUserMessages(userID, msgType, box, trash, messageID)
		End Function

		Private Shared Function GetUserMessages(ByVal userID As Integer, ByVal box As BoxType, ByVal read As Boolean) As ArrayList
			' Inbox messages include bulletins
			' Outbox messages include only messages
			Dim msgType As MessageType = IIf((box = BoxType.btInbox),MessageType.mtAny,MessageType.mtMail)
			Return GetUserMessages(userID, msgType, box, read)
		End Function

		Private Shared Function GetUserMessages(ByVal userID As Integer, ByVal box As BoxType, ByVal read As Boolean, ByVal trash As Boolean) As ArrayList
			' Inbox messages include bulletins
			' Outbox messages include only messages
			Dim msgType As MessageType = IIf((box = BoxType.btInbox),MessageType.mtAny,MessageType.mtMail)
			Return GetUserMessages(userID, msgType, box, read, trash)
		End Function


		'
		' Bulletins
		'

		Public Shared Function GetUserInboxBulletin(ByVal userID As Integer, ByVal messageID As Integer, ByVal trash As Boolean) As BulletinMessage
			Dim msgList As ArrayList = GetUserBulletins(userID, BoxType.btInbox, trash, messageID)
			Return DirectCast(msgList(0), BulletinMessage)
		End Function

		Public Shared Function GetUserInboxPreviousBulletin(ByVal userID As Integer, ByVal greaterThanMessageID As Integer) As BulletinMessage
			Dim msgList As ArrayList = GetUserMessages(userID, MessageType.mtBulletin, BoxType.btAny, False, -1, greaterThanMessageID)
			If msgList.Count = 0 Then
				Return Nothing
			End If

			Return DirectCast(msgList(0), BulletinMessage)
		End Function

		Public Shared Function GetUserInboxNextBulletin(ByVal userID As Integer, ByVal lessThanMessageID As Integer) As BulletinMessage
			Dim msgList As ArrayList = GetUserMessages(userID, MessageType.mtBulletin, BoxType.btAny, False, lessThanMessageID, -1)
			If msgList.Count = 0 Then
				Return Nothing
			End If

			Return DirectCast(msgList(0), BulletinMessage)
		End Function

		Public Shared Function GetUserOutboxBulletin(ByVal userID As Integer, ByVal messageID As Integer, ByVal trash As Boolean) As BulletinMessage
			Dim msgList As ArrayList = GetUserBulletins(userID, BoxType.btOutbox, trash, messageID)
			Return DirectCast(msgList(0), BulletinMessage)
		End Function

		' get all bulletins sent and received
		Public Shared Function GetAllUserBulletins(ByVal userID As Integer, ByVal trash As Boolean) As ArrayList
			Return GetUserBulletins(userID, BoxType.btAny, trash, -1)
		End Function
		Public Shared Function GetAllUserBulletins(ByVal userID As Integer, ByVal trash As Boolean, ByVal read As Boolean) As ArrayList
			Return GetUserBulletins(userID, BoxType.btAny, read, trash)
		End Function

		' get a bulletin 
		Public Shared Function GetUserBulletin(ByVal userID As Integer, ByVal messageID As Integer, ByVal trash As Boolean) As BulletinMessage
			Dim msgList As ArrayList = GetUserBulletins(userID, BoxType.btAny, trash, messageID)
			Return DirectCast(msgList(0), BulletinMessage)
		End Function

		Public Shared Function GetUserOutboxUnreadBulletins(ByVal userID As Integer) As ArrayList
			Return GetUserBulletins(userID, BoxType.btOutbox, False)
		End Function

		Public Shared Function GetUserOutboxUnreadBulletins(ByVal userID As Integer, ByVal trash As Boolean) As ArrayList
			Return GetUserBulletins(userID, BoxType.btOutbox, False, trash)
		End Function

		Private Shared Function GetUserBulletins(ByVal userID As Integer, ByVal box As BoxType, ByVal trash As Boolean, ByVal messageID As Integer) As ArrayList
			Return GetUserMessages(userID, MessageType.mtBulletin, box, trash, messageID)
		End Function

		Private Shared Function GetUserBulletins(ByVal userID As Integer, ByVal box As BoxType, ByVal read As Boolean) As ArrayList
			Return GetUserMessages(userID, MessageType.mtBulletin, box, read)
		End Function

		Private Shared Function GetUserBulletins(ByVal userID As Integer, ByVal box As BoxType, ByVal read As Boolean, ByVal trash As Boolean) As ArrayList
			Return GetUserMessages(userID, MessageType.mtBulletin, box, read, trash)
		End Function

		'
		' Common (Messages, Bulletins)
		'

		Private Shared Function GetUserMessages(ByVal userID As Integer, ByVal msgType As MessageType, ByVal box As BoxType, ByVal messageID As Integer) As ArrayList
			Return GetUserMessages(userID, msgType, box, False, False, False, _
				False, messageID, -1, -1)
		End Function

		Private Shared Function GetUserMessages(ByVal userID As Integer, ByVal msgType As MessageType, ByVal box As BoxType, ByVal read As Boolean) As ArrayList
			Return GetUserMessages(userID, msgType, box, True, read, False, _
				False, -1, -1, -1)
		End Function

		Private Shared Function GetUserMessages(ByVal userID As Integer, ByVal msgType As MessageType, ByVal box As BoxType, ByVal read As Boolean, ByVal trash As Boolean) As ArrayList
			Return GetUserMessages(userID, msgType, box, True, read, True, _
				trash, -1, -1, -1)
		End Function

		Private Shared Function GetUserMessages(ByVal userID As Integer, ByVal msgType As MessageType, ByVal box As BoxType, ByVal trash As Boolean, ByVal messageID As Integer) As ArrayList
			Return GetUserMessages(userID, msgType, box, False, False, True, _
				trash, messageID, -1, -1)
		End Function

		Private Shared Function GetUserMessages(ByVal userID As Integer, ByVal msgType As MessageType, ByVal box As BoxType, ByVal trash As Boolean, ByVal lessThanMessageID As Integer, ByVal greaterThanMessageID As Integer) As ArrayList
			Return GetUserMessages(userID, msgType, box, False, False, True, _
				trash, -1, lessThanMessageID, greaterThanMessageID)
		End Function

		Private Shared Function GetUserMessages(ByVal userID As Integer, ByVal msgType As MessageType, ByVal box As BoxType, ByVal readMatters As Boolean, ByVal read As Boolean, ByVal trashMatters As Boolean, _
			ByVal trash As Boolean, ByVal messageID As Integer, ByVal lessThanMessageID As Integer, ByVal greaterThanMessageID As Integer) As ArrayList
			Dim msgs As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()
			Try
				Dim cmd As New SqlCommand(GetUserMessagesSQL(userID, msgType, box, readMatters, read, trashMatters, _
					trash, messageID, lessThanMessageID, greaterThanMessageID))
				cmd.Connection = conn

				Dim dr As SqlDataReader = cmd.ExecuteReader()
				While dr.Read()
					Dim mtRead As MessageType = DirectCast(dr.GetInt32(dr.GetOrdinal("MessageType")), MessageType)

					If mtRead = MessageType.mtBulletin Then
						msgs.Add(New BulletinMessage(dr))
					Else
						msgs.Add(New Message(dr))
					End If
				End While
			Finally
				conn.Close()
			End Try

			Return msgs
		End Function

		' compose the where clause filtering inbox messages for a user
		Private Shared Function GetUserMessageInboxSQL(ByVal userID As Integer) As String
			Dim sql As String = "Box=" + DirectCast(BoxType.btInbox, Integer) + Environment.NewLine
			sql += "AND ToUID=" + userID
			Return sql
		End Function

		' compose the where clause filtering outbox messages for a user
		Private Shared Function GetUserMessageOutboxSQL(ByVal userID As Integer) As String
			Dim sql As String = "Box=" + DirectCast(BoxType.btOutbox, Integer) + Environment.NewLine
			sql += "AND FromUID=" + userID
			Return sql
		End Function

		Private Shared Function GetUserMessagesSQL(ByVal userID As Integer, ByVal msgType As MessageType, ByVal box As BoxType, ByVal readMatters As Boolean, ByVal read As Boolean, ByVal trashMatters As Boolean, _
			ByVal trash As Boolean, ByVal messageID As Integer, ByVal lessThanMessageID As Integer, ByVal greaterThanMessageID As Integer) As String
			Dim sql As String = "SELECT * FROM Messages WHERE" + Environment.NewLine

			' Filter the user and the box type
			Dim includeAllBulletins As Boolean = ((msgType = MessageType.mtMail) AndAlso (box = BoxType.btInbox) AndAlso Not readMatters)
			If includeAllBulletins Then
				sql += "(("
			End If

			Select Case box
				Case BoxType.btInbox
					sql += GetUserMessageInboxSQL(userID) + Environment.NewLine
					Exit Select
				Case BoxType.btOutbox

					sql += GetUserMessageOutboxSQL(userID) + Environment.NewLine
					Exit Select
				Case BoxType.btAny

					sql += "((" + GetUserMessageInboxSQL(userID) + ")" + Environment.NewLine
					sql += "OR (" + GetUserMessageOutboxSQL(userID) + "))" + Environment.NewLine
					Exit Select
				Case Else

					Throw New MessageException("Unknown box type (know are inbox,outbox,any)")
			End Select

			' filter the message type (message / bulletin)
			If msgType <> MessageType.mtAny Then
				sql += "AND MessageType=" + DirectCast(msgType, Integer) + Environment.NewLine
			End If

			If includeAllBulletins Then
				sql += ")" + Environment.NewLine
				sql += " OR ( MessageType=" + DirectCast(MessageType.mtBulletin, Integer) + Environment.NewLine
				sql += "AND ((ToUID=" + userID + " AND Box=" + DirectCast(BoxType.btInbox, Integer) + ") OR (FromUID=" + userID + " AND Box=" + DirectCast(BoxType.btOutbox, Integer) + "))" + Environment.NewLine
				sql += ")" + Environment.NewLine
				sql += ")" + Environment.NewLine
			End If

			If trashMatters Then
				' filter (non) trash messages
				sql += "AND StatusDeleted = " + (IIf(trash,1,0)) + Environment.NewLine
			End If

			If readMatters Then
				' filter read/unread messages
				sql += "AND StatusRead = " + (IIf(read,1,0)) + Environment.NewLine
			End If

			If messageID >= 0 Then
				sql += "AND ID = " + messageID + Environment.NewLine
ElseIf lessThanMessageID >= 0 Then
				sql += "AND ID < " + lessThanMessageID + Environment.NewLine
ElseIf greaterThanMessageID >= 0 Then
				sql += "AND ID > " + greaterThanMessageID + Environment.NewLine
			End If

			If greaterThanMessageID >= 0 Then
				sql += "ORDER BY Date Asc, ID Asc" + Environment.NewLine
			Else
				sql += "ORDER BY Date Desc, ID Desc" + Environment.NewLine
			End If
			Return sql
		End Function

		''' <summary>
		''' Comparator class for Message and descendands
		''' </summary>
		Protected Class MessageSorter
			Implements IComparer
			Private ascFactor As Integer

			Public Sub New(ByVal bAsc As Boolean)
				ascFactor = IIf(bAsc,1,-1)
			End Sub

			#region IComparer Members

			Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer
				Dim xMsg As Message = DirectCast(x, Message)
				Dim yMsg As Message = DirectCast(y, Message)
				Return ascFactor * xMsg.DateSent.CompareTo(yMsg.DateSent)
			End Function
#End Region
		End Class
	End Class
End Namespace






